<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <title>ActiveRecord::Querying</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <link rel="stylesheet" href="../../css/reset.css" type="text/css" media="screen" />
<link rel="stylesheet" href="../../css/main.css" type="text/css" media="screen" />
<link rel="stylesheet" href="../../css/github.css" type="text/css" media="screen" />
<script src="../../js/jquery-1.3.2.min.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/jquery-effect.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/main.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/highlight.pack.js" type="text/javascript" charset="utf-8"></script>

</head>

<body>     
    <div class="banner">
        
            <span>Ruby on Rails v4.0.0</span><br />
        
        <h1>
            <span class="type">Module</span> 
            ActiveRecord::Querying 
            
        </h1>
        <ul class="files">
            
            <li><a href="../../files/activerecord/lib/active_record/querying_rb.html">activerecord/lib/active_record/querying.rb</a></li>
            
        </ul>
    </div>
    <div id="bodyContent">
        <div id="content">
  


  


  
  


  


  
    <!-- Method ref -->
    <div class="sectiontitle">Methods</div>
    <dl class="methods">
      
        <dt>C</dt>
        <dd>
          <ul>
            
              
              <li>
                <a href="Querying.html#method-i-count_by_sql">count_by_sql</a>
              </li>
            
          </ul>
        </dd>
      
        <dt>F</dt>
        <dd>
          <ul>
            
              
              <li>
                <a href="Querying.html#method-i-find_by_sql">find_by_sql</a>
              </li>
            
          </ul>
        </dd>
      
    </dl>
  

  



  

    

    

    


    


    <!-- Methods -->
        
      <div class="sectiontitle">Instance Public methods</div>
      
        <div class="method">
          <div class="title method-title" id="method-i-count_by_sql">
            
              <b>count_by_sql</b>(sql)
            
            <a href="Querying.html#method-i-count_by_sql" name="method-i-count_by_sql" class="permalink">Link</a>
          </div>
          
          
            <div class="description">
              <p>Returns the result of an SQL statement that should only include a COUNT(*)
in the SELECT part. The use of this method should be restricted to
complicated SQL queries that can’t be executed using the <a
href="Calculations.html">ActiveRecord::Calculations</a> class methods. Look
into those before using this.</p>

<h4 id="method-i-count_by_sql-label-Parameters">Parameters</h4>
<ul><li>
<p><code>sql</code> - An SQL statement which should return a count query from
the database, see the example below.</p>

<p>Product.count_by_sql “SELECT COUNT(*) FROM sales s, customers c WHERE
s.customer_id = c.id”</p>
</li></ul>
            </div>
          
          
          
          
          
            
            <div class="sourcecode">
              
              <p class="source-link">
                Source: 
                <a href="javascript:toggleSource('method-i-count_by_sql_source')" id="l_method-i-count_by_sql_source">show</a>
                
                  | <a href="https://github.com/rails/rails/blob/85a56ff01de71d4978e809d86920513a95ddf3a0/activerecord/lib/active_record/querying.rb#L57" target="_blank" class="github_url">on GitHub</a>
                
              </p>
              <div id="method-i-count_by_sql_source" class="dyn-source">
                <pre><span class="ruby-comment"># File activerecord/lib/active_record/querying.rb, line 57</span>
<span class="ruby-keyword">def</span> <span class="ruby-keyword ruby-title">count_by_sql</span>(<span class="ruby-identifier">sql</span>)
  <span class="ruby-identifier">sql</span> = <span class="ruby-identifier">sanitize_conditions</span>(<span class="ruby-identifier">sql</span>)
  <span class="ruby-identifier">connection</span>.<span class="ruby-identifier">select_value</span>(<span class="ruby-identifier">sql</span>, <span class="ruby-node">&quot;#{name} Count&quot;</span>).<span class="ruby-identifier">to_i</span>
<span class="ruby-keyword">end</span></pre>
              </div>
            </div>
            
          </div>
        
        <div class="method">
          <div class="title method-title" id="method-i-find_by_sql">
            
              <b>find_by_sql</b>(sql, binds = [])
            
            <a href="Querying.html#method-i-find_by_sql" name="method-i-find_by_sql" class="permalink">Link</a>
          </div>
          
          
            <div class="description">
              <p>Executes a custom SQL query against your database and returns all the
results. The results will be returned as an array with columns requested
encapsulated as attributes of the model you call this method from. If you
call <code>Product.find_by_sql</code> then the results will be returned in
a Product object with the attributes you specified in the SQL query.</p>

<p>If you call a complicated SQL query which spans multiple tables the columns
specified by the SELECT will be attributes of the model, whether or not
they are columns of the corresponding table.</p>

<p>The <code>sql</code> parameter is a full SQL query as a string. It will be
called as is, there will be no database agnostic conversions performed.
This should be a last resort because using, for example, MySQL specific
terms will lock you to using that particular database engine or require you
to change your call if you switch engines.</p>

<pre class="ruby"><span class="ruby-comment"># A simple SQL query spanning multiple tables</span>
<span class="ruby-constant">Post</span>.<span class="ruby-identifier">find_by_sql</span> <span class="ruby-string">&quot;SELECT p.title, c.author FROM posts p, comments c WHERE p.id = c.post_id&quot;</span>
<span class="ruby-comment"># =&gt; [#&lt;Post:0x36bff9c @attributes={&quot;title&quot;=&gt;&quot;Ruby Meetup&quot;, &quot;first_name&quot;=&gt;&quot;Quentin&quot;}&gt;, ...]</span>

<span class="ruby-comment"># You can use the same string replacement techniques as you can with ActiveRecord#find</span>
<span class="ruby-constant">Post</span>.<span class="ruby-identifier">find_by_sql</span> [<span class="ruby-string">&quot;SELECT title FROM posts WHERE author = ? AND created &gt; ?&quot;</span>, <span class="ruby-identifier">author_id</span>, <span class="ruby-identifier">start_date</span>]
<span class="ruby-comment"># =&gt; [#&lt;Post:0x36bff9c @attributes={&quot;title&quot;=&gt;&quot;The Cheap Man Buys Twice&quot;}&gt;, ...]</span>
</pre>
            </div>
          
          
          
          
          
            
            <div class="sourcecode">
              
              <p class="source-link">
                Source: 
                <a href="javascript:toggleSource('method-i-find_by_sql_source')" id="l_method-i-find_by_sql_source">show</a>
                
                  | <a href="https://github.com/rails/rails/blob/85a56ff01de71d4978e809d86920513a95ddf3a0/activerecord/lib/active_record/querying.rb#L35" target="_blank" class="github_url">on GitHub</a>
                
              </p>
              <div id="method-i-find_by_sql_source" class="dyn-source">
                <pre><span class="ruby-comment"># File activerecord/lib/active_record/querying.rb, line 35</span>
<span class="ruby-keyword">def</span> <span class="ruby-keyword ruby-title">find_by_sql</span>(<span class="ruby-identifier">sql</span>, <span class="ruby-identifier">binds</span> = [])
  <span class="ruby-identifier">result_set</span> = <span class="ruby-identifier">connection</span>.<span class="ruby-identifier">select_all</span>(<span class="ruby-identifier">sanitize_sql</span>(<span class="ruby-identifier">sql</span>), <span class="ruby-node">&quot;#{name} Load&quot;</span>, <span class="ruby-identifier">binds</span>)
  <span class="ruby-identifier">column_types</span> = {}

  <span class="ruby-keyword">if</span> <span class="ruby-identifier">result_set</span>.<span class="ruby-identifier">respond_to?</span> <span class="ruby-value">:column_types</span>
    <span class="ruby-identifier">column_types</span> = <span class="ruby-identifier">result_set</span>.<span class="ruby-identifier">column_types</span>
  <span class="ruby-keyword">else</span>
    <span class="ruby-constant">ActiveSupport</span><span class="ruby-operator">::</span><span class="ruby-constant">Deprecation</span>.<span class="ruby-identifier">warn</span> <span class="ruby-string">&quot;the object returned from `select_all` must respond to `column_types`&quot;</span>
  <span class="ruby-keyword">end</span>

  <span class="ruby-identifier">result_set</span>.<span class="ruby-identifier">map</span> { <span class="ruby-operator">|</span><span class="ruby-identifier">record</span><span class="ruby-operator">|</span> <span class="ruby-identifier">instantiate</span>(<span class="ruby-identifier">record</span>, <span class="ruby-identifier">column_types</span>) }
<span class="ruby-keyword">end</span></pre>
              </div>
            </div>
            
          </div>
                    </div>

    </div>
  </body>
</html>    